-- Create a new relational table with 3 columns
-- DROP TABLE ygb;

-- CREATE TABLE ygb 
-- (
--   id NUMBER(10) NOT NULL,
--   ENAME VARCHAR2(1024),
--   PSAL NUMBER(7,2),
--   DNAME VARCHAR2(2),
--   RIQI NUMBER(10),
--   MG NUMBER(10)
-- )

-- insert into ygb (id,ENAME,PSAL,DANME,RIQI)VALUES(1,'zhansan',100.00,'A',20080101);
-- insert into ygb VALUES(2,'lisi',200.00,'A',20070101,1);
-- insert into ygb VALUES(3,'wangwu',300.00,'C',20160101,2);
-- insert into ygb VALUES(4,'zhaoliu',242.00,'D',20050909,1);
-- insert into ygb VALUES(5,'yanqi',780.00,'D',20120202,3);
-- insert into ygb VALUES(6,'wuba',600.00,'B',20100101,2);
-- insert into ygb VALUES(7,'aab',300.00,'C',20180202,6);
-- insert into ygb VALUES(8,'cbd',400.00,'B',20050101,6);
-- insert into ygb VALUES(9,'cny',200.00,'B',20040101,7);
-- insert into ygb VALUES(10,'am',100.00,'A',20180101,1);
-- insert into ygb VALUES(11,'al',100.00,'A',20180102,1);

-- SELECT * FROM ygb WHERE id in (1,2,3,4,5);
-- SELECT * FROM ygb;


create or replace procedure pro1(id1 number,id2 number,sall number)
as
begin
update ygb set psal=(CASE WHEN id=id1 then sall
                          WHEN id=id2 then sall+200 
  END)
where id IN (id1,id2);
commit;
end;


BEGIN
pro1(4,5,220);
END;
SHOW ERROR

--Create a new Procedure

-- Procedure definition
create TABLE ygb_test(
dname VARCHAR2(2),  --部门
id NUMBER(10),      --员工ID
riqi NUMBER(10)     --入职日期
)


CREATE or REPLACE PROCEDURE sp_PROCEDURE1 (
  v_dname VARCHAR2) AS
-- v_dname VARCHAR2(10)
BEGIN
INSERT INTO ygb_test(
  dname, --部门
  id,    --员工ID
  riqi   --入职日期
)
SELECT b.dname,
       b.id,
       b.riqi
FROM(
SELECT a.dname,
       a.id,
       a.riqi,
       row_number() over(PARTITION by v_dname ORDER BY riqi) as rn
FROM ygb a)b
WHERE b.rn <11;
commit;
END;
/

BEGIN
sp_PROCEDURE1('A');
END;


CREATE TABLE ygb_par
(
  id    NUMBER(10) NOT NULL,
  ENAME VARCHAR2(1024),
  PSAL  NUMBER(7,2),
  DNAME VARCHAR2(2),
  RIQI  NUMBER(10),
  MG    NUMBER(10)
)
partition by range(id)
(
  partition p1 values less than (5),
  partition p2 values less than (10),
  partition p3 values less than (15)
);
insert into ygb_par select * from ygb;